package com.zysoft.function;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import android.widget.Toast;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;
import java.util.UUID;

/**
  * @author wmf
  * 用法：
  * DBHelper dbHelper = new DBHelper(this);
  * dbHelper.createDataBase();
  * SQLiteDatabase db = dbHelper.getWritableDatabase();
  * Cursor cursor = db.query()
  * db.execSQL(sqlString);
  * 注意：execSQL不支持带;的多条SQL语句，只能一条一条的执行
  * 将把assets下的数据库文件直接复制到sdk，但数据库文件大小限制在1M以下
  * 不支持超过1M的数据
  */
public class MyDatabaseHelper extends SQLiteOpenHelper {

    private static final int DB_VERSION    = 1;
    private static String DATABASE_PATH = android.os.Environment.getExternalStorageDirectory().getAbsolutePath() + "/zysoft/database/";
    private static String DB_NAME = "terminal.db";

    private Context mContext;

    public MyDatabaseHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, null, version);
        this.mContext = context;
    }

    public MyDatabaseHelper(Context context, String name, int version){
        this(context,name,null,version);
    }

    public MyDatabaseHelper(Context context, String name){
        this(context,name,DB_VERSION);
    }

    public MyDatabaseHelper (Context context) {
        this(context, DATABASE_PATH + DB_NAME);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {

    }
    public void createDataBase() {
        boolean is = CheckDataBase();
        if(!is){
            try {
                File dir = new File(DATABASE_PATH);
                if(!dir.exists()){
                    dir.mkdirs();
                }
                File dbf = new File(DATABASE_PATH + DB_NAME);
                if(dbf.exists()){
                    dbf.delete();
                }
                SQLiteDatabase.openOrCreateDatabase(dbf, null);
                // 复制asseets中的db文件到DB_PATH下
                CopyDataBase();
            } catch (IOException e) {
                Log.e("zysoft","数据库创建失败");
                Toast.makeText(mContext, "数据库创建失败", Toast.LENGTH_SHORT).show();
            }
        }else {
            Log.e("zysodt","数据库存在");
        }
//        db.execSQL(CREATE_PROJECT);
//        Log.e("zysodt","数据库创建成功");
//        Toast.makeText(mContext, "数据库创建成功", Toast.LENGTH_SHORT).show();
    }
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {

    }
    //判断数据库是否存在
    public Boolean CheckDataBase(){
        SQLiteDatabase check = null;
        try{
            check = SQLiteDatabase.openDatabase(DATABASE_PATH + DB_NAME, null, SQLiteDatabase.OPEN_READONLY);
        }catch(SQLiteException e){
            Log.e("zysoft","异常信息："+e.getMessage());
        }
        if(check != null){
            check.close();
        }
        return check != null ? true : false;
    }
    public void CopyDataBase() throws IOException {
        InputStream input = mContext.getAssets().open(DB_NAME);
        OutputStream Output = new FileOutputStream(DATABASE_PATH + DB_NAME);
        byte[] buffer = new byte[1024];
        int length;
        while ((length = input.read(buffer))>0){
            Output.write(buffer, 0, length);
        }
        Output.flush();
        Output.close();
        input.close();
    }
    /**
      * ************************************************************
      * @method  insert
      * @description 录入人员离线数据
      * @date: 2019/10/15 11:28
      * @author: wmf
      * @return
      * ************************************************************
     */
    public int insert(Map info){
        Log.e("zysoft",info.get("corpCode").toString());
        SQLiteDatabase db  = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("id",info.get("id").toString());//id
        values.put("corpCode", info.get("corpCode").toString());//公司编码
        values.put("projectCode", info.get("projectCode").toString());//项目编码
        values.put("name", info.get("name").toString());//姓名
        values.put("gender",info.get("gender").toString());//性别
        values.put("nation", info.get("nation").toString());//名族
        values.put("birthDay",info.get("birthDay").toString());//出生日期
        values.put("idCardType",info.get("idCardType").toString());//证件类型
        values.put("idCardNumber", info.get("idCardNumber").toString());//证件号码
        values.put("startTime", info.get("startTime").toString());//有效期(开始)
        values.put("expiryTime", info.get("expiryTime").toString());//有效期（结束）
        values.put("grantOrg",info.get("grantOrg").toString());//签发机关
        values.put("birthPlaceCode",info.get("birthPlaceCode").toString());//籍贯
        values.put("address", info.get("address").toString());//家庭住址
        values.put("cellPhone", info.get("cellPhone").toString());//联系电话
        values.put("hasBadMedicalHistory",info.get("hasBadMedicalHistory").toString());//重大病史
        values.put("medicalHistory",info.get("medicalHistory").toString());//病史说明
        values.put("headImageUrl",info.get("headImageUrl").toString());//身份证头像
        values.put("liveImageUrl",info.get("liveImageUrl").toString());//现场人员照片
        values.put("fingerprint",info.get("fingerprint").toString());//指纹图片
        values.put("signId",info.get("signId").toString());//签名
        values.put("bloodType", info.get("bloodType").toString());//血型
        values.put("workerType", info.get("workerType").toString());//人员类别
        values.put("workTypeCode",info.get("workTypeCode").toString());//工种
        values.put("isSpecialWorkType",info.get("isSpecialWorkType").toString());//特种工种
        values.put("jobType",info.get("jobType").toString());//岗位类型
        values.put("entryTime",info.get("entryTime").toString());//进场日期
        values.put("status", info.get("status").toString());//状态
        values.put("type", info.get("type").toString());//设备类型 默认 4
        values.put("productCode", info.get("productCode").toString());//设备标识
        values.put("idCardImageUrl",info.get("idCardImageUrl").toString());//证件照图片
        values.put("workClassId", info.get("workClassId").toString());//班组id
        values.put("isTeamLeader",info.get("isTeamLeader").toString());//班组长
        int ret = (int)db.insert("project", (String)null, values);
        db.close();
        return ret;
    }
    //插入岗位类型字典项数据
    public int inserData(String data){
        String[] id = {String.valueOf(1)};
        delete("delete from Dictionary");//更新前全部清空表中所有数据
        SQLiteDatabase db  = getWritableDatabase();
        ContentValues value = new ContentValues();
        value.put("data", data);//插入数据
        int ret = (int)db.insert("Dictionary",null,value);
        Log.e("zysoft","插入数据结果:"+ret);//不等于-1表示插入成功
        db.close();
        return ret;
    }
    //插入班组字典项数据
    public int inserTeam(String data){
        String[] id = {String.valueOf(1)};
        delete("delete from team");//更新前全部清空表中所有数据
        SQLiteDatabase db  = getWritableDatabase();//db.rawQuery("UPDATE team SET team = data WHERE id = 1",null);
        ContentValues value = new ContentValues();
        value.put("team",data);
        int ret = (int)db.insert("team",null,value);
        db.close();
        return ret;
    }
    //插入人员退场数据
    public int inserPeron(String data){
        SQLiteDatabase db  = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("id",uuid());
        values.put("oid", data);//插入数据
        int ret = (int)db.insert("exitPerson", (String)null, values);
        Log.e("zysoft","插入数据结果"+ret);//不等于-1表示插入成功
        db.close();
        return ret;
    }
    public void DeleteExit(){
        delete("delete from exitPerson");
    }
    public void DeteleTeam(){
        delete("delete from addTeam");
    }
    //插入人员退场数据
    public int inserExit(String data){
        String[] id = {String.valueOf(1)};
        delete("delete from Identity");//更新前全部清空表中所有数据
        SQLiteDatabase db  = getWritableDatabase();
        ContentValues value = new ContentValues();
        value.put("Identity", data);//插入数据
        int ret = (int)db.insert("Identity", null,value);
        Log.e("zysoft","插入数据结果"+ret);//不等于-1表示插入成功
        db.close();
        return ret;
    }
    //插入班组
    public int inserTeamAdd(Map info){
        SQLiteDatabase db  = getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put("masterId",info.get("masterId").toString());
        values.put("masterName", info.get("masterName").toString());//班组名称
        values.put("workType", info.get("workType").toString());//工种类型
        values.put("workTeamId", info.get("workTeamId").toString());//作业队id
        int ret = (int)db.insert("addTeam", (String)null, values);
        Log.e("zysoft","插入数据结果"+ret);//不等于-1表示插入成功
        db.close();
        return ret;
    }
    //删除表中所有数据
    public void delete(String sql){
        SQLiteDatabase db  = this.getWritableDatabase();
        db.execSQL(sql);
        db.close();
    }
    //查询退场人员离线数据
    public String findExitPerson(){
        String res = "";
        Log.e("zysoft","离线查询数据表");
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from Identity",null);
        if(cursor.moveToFirst()){
            res = cursor.getString(cursor.getColumnIndex("Identity"));
            Log.e("zysoft_Offline",cursor.getString(cursor.getColumnIndex("Identity")));
        }
        cursor.close();
        db.close();
        return res;
    }
    //查询离线班组
    public String findExitTeam(){
        String res = "";
        Log.e("zysoft","离线查询数据表");
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT team FROM team",null);
        if(cursor.moveToFirst()){
            res = cursor.getString(cursor.getColumnIndex("team"));
            Log.e("zysoft_Offline",cursor.getString(cursor.getColumnIndex("team")));
        }
        cursor.close();
        db.close();
        return res;
    }
    //查询离线班组
    public String findType(){
        String res = "";
        Log.e("zysoft","离线查询数据表");
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT data FROM Dictionary",null);
        if(cursor.moveToFirst()){
            res = cursor.getString(cursor.getColumnIndex("data"));
            if(res == null){
                cursor.close();
                db.close();
                return "0";
            }
        }
        cursor.close();
        db.close();
        return res;
    }
    /**
      * ************************************************************
      * @method  DeleteOne
      * @description 删除数据库中的一条数据
      * @date: 2019/10/15 11:27
      * @author: wmf
      * @return
      * ************************************************************
     */
    public void DeleteOne(String sql){
        SQLiteDatabase db  = this.getWritableDatabase();
        db.execSQL(sql);
        db.close();
    }
    //查询单条离线录入人员数据
    public HashMap<String, Object> Select(String ...args){
        if(args.length>0){//如果id存在 删除词条数据
            String[] id = {String.valueOf(args[0])};
            Log.e("zysoft",args[0]);
            Log.e("zysoft","删除数据");
            SQLiteDatabase db  = this.getWritableDatabase();
            db.delete("project","id=?",id);
            db.close();
        }
        HashMap<String, Object> map = new HashMap<String, Object>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from project",null);
        if(cursor.moveToFirst()){
            map.put("id",cursor.getString(cursor.getColumnIndex("id")));
            map.put("corpCode", cursor.getString(cursor.getColumnIndex("corpCode")));
            map.put("projectCode", cursor.getString(cursor.getColumnIndex("projectCode")));
            map.put("name", cursor.getString(cursor.getColumnIndex("name")));
            map.put("gender", cursor.getString(cursor.getColumnIndex("gender")));
            map.put("nation", cursor.getString(cursor.getColumnIndex("nation")));
            map.put("birthDay", cursor.getString(cursor.getColumnIndex("birthDay")));
            map.put("idCardType", cursor.getString(cursor.getColumnIndex("idCardType")));
            map.put("idCardNumber", cursor.getString(cursor.getColumnIndex("idCardNumber")));
            map.put("startTime", cursor.getString(cursor.getColumnIndex("startTime")));
            map.put("expiryTime", cursor.getString(cursor.getColumnIndex("expiryTime")));
            map.put("grantOrg", cursor.getString(cursor.getColumnIndex("grantOrg")));
            map.put("birthPlaceCode", cursor.getString(cursor.getColumnIndex("birthPlaceCode")));
            map.put("address", cursor.getString(cursor.getColumnIndex("address")));
            map.put("cellPhone", cursor.getString(cursor.getColumnIndex("cellPhone")));
            map.put("hasBadMedicalHistory", cursor.getString(cursor.getColumnIndex("hasBadMedicalHistory")));
            map.put("headImageUrl", cursor.getString(cursor.getColumnIndex("headImageUrl")));
            map.put("liveImageUrl", cursor.getString(cursor.getColumnIndex("liveImageUrl")));
            map.put("fingerprint", cursor.getString(cursor.getColumnIndex("fingerprint")));
            map.put("signId", cursor.getString(cursor.getColumnIndex("signId")));
            map.put("bloodType", cursor.getString(cursor.getColumnIndex("bloodType")));
            map.put("workerType", cursor.getString(cursor.getColumnIndex("workerType")));
            map.put("workTypeCode", cursor.getString(cursor.getColumnIndex("workTypeCode")));
            map.put("medicalHistory",cursor.getString(cursor.getColumnIndex("medicalHistory")));
            map.put("jobType", cursor.getString(cursor.getColumnIndex("jobType")));
            map.put("entryTime", cursor.getString(cursor.getColumnIndex("entryTime")));
            map.put("status", cursor.getString(cursor.getColumnIndex("status")));
            map.put("type", cursor.getString(cursor.getColumnIndex("type")));
            map.put("productCode", cursor.getString(cursor.getColumnIndex("productCode")));
            map.put("idCardImageUrl", cursor.getString(cursor.getColumnIndex("idCardImageUrl")));
            map.put("workClassId", cursor.getString(cursor.getColumnIndex("workClassId")));
            map.put("isSpecialWorkType", cursor.getString(cursor.getColumnIndex("isSpecialWorkType")));
            map.put("isTeamLeader", cursor.getString(cursor.getColumnIndex("isTeamLeader")));
        }
        cursor.close();
        db.close();
        return map;
    }
    /**
      * ************************************************************
      * @method  findTeam
      * @description 查询离线添加的班组
      * @date: 2019/10/15 17:26
      * @author: wmf
      * @return  ArrayList
      * ************************************************************
     */
    public ArrayList<HashMap<String, Object>> findTeam(){
        Log.e("zysoft","离线查询班组");
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from addTeam",null);
        ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        while (cursor.moveToNext()) {
            HashMap<String, Object> map = new HashMap<String, Object>();
            map.put("masterId", cursor.getString(cursor.getColumnIndex("masterId")));
            map.put("masterName", cursor.getString(cursor.getColumnIndex("masterName")));
            map.put("workType", cursor.getString(cursor.getColumnIndex("workType")));
            map.put("workTeamId", cursor.getString(cursor.getColumnIndex("workTeamId")));
            list.add(map);
        }
        cursor.close();
        db.close();
        return list;
    }
    /**     
      * ************************************************************
      * @method  findExit
      * @description 查询退场人员数据
      * @date: 2019/10/15 20:35
      * @author: wmf
      * @return ArrayList
      * ************************************************************
     */
    public ArrayList<HashMap<String, Object>> findExit(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from exitPerson",null);
        ArrayList<HashMap<String, Object>> person = new ArrayList<HashMap<String, Object>>();
        while (cursor.moveToNext()) {
            HashMap<String, Object> map = new HashMap<String, Object>();
            map.put("oid", cursor.getString(cursor.getColumnIndex("oid")));
            person.add(map);
        }
        cursor.close();
        db.close();
        return person;
    }
    /**
      * ************************************************************
      * @method  findPerson
      * @description 查询离线录入的人员数据
      * @date: 2019/10/15 17:27
      * @author: wmf
      * @return ArrayList
      * ************************************************************
     */
    public ArrayList<HashMap<String, Object>> findPerson(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from project",null);
        ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        while (cursor.moveToNext()) {//cursor.getCount()查询有多少条数据
            HashMap<String, Object> map = new HashMap<String, Object>();
            map.put("corpCode", cursor.getInt(cursor.getColumnIndex("corpCode")));
            map.put("projectCode", cursor.getString(cursor.getColumnIndex("projectCode")));
            map.put("name", cursor.getString(cursor.getColumnIndex("name")));
            map.put("gender", cursor.getString(cursor.getColumnIndex("gender")));
            map.put("nation", cursor.getInt(cursor.getColumnIndex("nation")));
            map.put("birthDay", cursor.getString(cursor.getColumnIndex("birthDay")));
            map.put("idCardType", cursor.getString(cursor.getColumnIndex("idCardType")));
            map.put("idCardNumber", cursor.getString(cursor.getColumnIndex("idCardNumber")));
            map.put("startTime", cursor.getInt(cursor.getColumnIndex("startTime")));
            map.put("expiryTime", cursor.getString(cursor.getColumnIndex("expiryTime")));
            map.put("grantOrg", cursor.getString(cursor.getColumnIndex("grantOrg")));
            map.put("birthPlaceCode", cursor.getString(cursor.getColumnIndex("birthPlaceCode")));
            map.put("address", cursor.getInt(cursor.getColumnIndex("address")));
            map.put("cellPhone", cursor.getString(cursor.getColumnIndex("cellPhone")));
            map.put("hasBadMedicalHistory", cursor.getString(cursor.getColumnIndex("hasBadMedicalHistory")));
            map.put("headImageUrl", cursor.getString(cursor.getColumnIndex("headImageUrl")));
            map.put("liveImageUrl", cursor.getInt(cursor.getColumnIndex("liveImageUrl")));
            map.put("fingerprint", cursor.getString(cursor.getColumnIndex("fingerprint")));
            map.put("signId", cursor.getString(cursor.getColumnIndex("signId")));
            map.put("bloodType", cursor.getString(cursor.getColumnIndex("bloodType")));
            map.put("workerType", cursor.getInt(cursor.getColumnIndex("workerType")));
            map.put("workTypeCode", cursor.getString(cursor.getColumnIndex("workTypeCode")));
            map.put("jobType", cursor.getString(cursor.getColumnIndex("jobType")));
            map.put("entryTime", cursor.getString(cursor.getColumnIndex("entryTime")));
            map.put("status", cursor.getInt(cursor.getColumnIndex("status")));
            map.put("medicalHistory",cursor.getString(cursor.getColumnIndex("medicalHistory")));
            map.put("type", cursor.getString(cursor.getColumnIndex("type")));
            map.put("productCode", cursor.getString(cursor.getColumnIndex("productCode")));
            map.put("idCardImageUrl", cursor.getString(cursor.getColumnIndex("idCardImageUrl")));
            map.put("workClassId", cursor.getInt(cursor.getColumnIndex("workClassId")));
            map.put("isSpecialWorkType", cursor.getString(cursor.getColumnIndex("isSpecialWorkType")));
            map.put("isTeamLeader", cursor.getString(cursor.getColumnIndex("isTeamLeader")));
            list.add(map);
        }
        cursor.close();
        db.close();
        return list;
    }
    //获取离线录入人员的数据条数
    public int getCountPerson(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from project",null);
        int num = cursor.getCount();
        cursor.close();
        db.close();
       return num;
    }
    //获取离线退场人员的数据条数
    public int getCountExit(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from exitPerson",null);
        int num = cursor.getCount();
        cursor.close();
        db.close();
        return num;
    }
    //获取离线添加班组的数据条数
    public int getCountTeam(){
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("select * from addTeam",null);
        int num = cursor.getCount();
        cursor.close();
        db.close();
        return num;
    }
    public String uuid(){
        String uuid = UUID.randomUUID().toString().replace("-", "");
        return uuid;
    }
}
